-- @owner: @qumin20145
-- @date: 2022-06-07
-- @testpoint: select目标列使用序列

--step1:建表; expect:成功
drop table if exists t_ustore_union_0032 cascade;
drop procedure if exists proc_ustore_union;

create table t_ustore_union_0032(
id int,c_int int,c_real real,c_float float,c_decimal decimal,c_number number,
c_char char(10),c_vchar varchar(10) not null,c_vchar2 varchar2(100),c_clob clob,
c_long varchar(200),c_blob blob,c_raw raw(100),c_date date,c_timestamp timestamp)
with (storage_type=ustore);

--step2:插入数据; expect:成功
insert into t_ustore_union_0032 values(1,1000,100.123,100.456,100.789,100.123,'abc123','abcdefg',
lpad('123abc',50,'abc'),lpad('123abc',50,'abc'),lpad('11100000',50,'1100'),lpad('11100001',50,'1100')::blob,lpad('11100011',50,'1100')::raw,to_timestamp(to_char('1800-01-01 10:51:47'),'yyyy-mm-dd hh24:mi:ss'),to_timestamp(to_char('1800-03-05 10:51:47'),'yyyy-mm-dd hh24:mi:ss'));
create or replace procedure proc_ustore_union(tname varchar,startall int,endall int) as
sqlst varchar(500);
begin
  for i in startall..endall loop
                sqlst := 'insert into ' || tname ||' select id+'||i||',c_int+'||i||',c_real+'||i||',c_float+'||i||',c_decimal+'||i||',c_number+'||i||',c_char'||',c_vchar||'||i||',c_vchar2||'||i||',c_clob||'||i||',c_long||'||i||',c_blob'||',c_raw,add_months(c_date,'||i||'),add_months(c_timestamp,'||i||') from '||tname|| ' where id=1';
        execute immediate sqlst;
  end loop;
end;
/

call proc_ustore_union('t_ustore_union_0032',1,20);

--step3:创建序列; expect:成功
drop sequence if exists seq_ustore_union_0032;
create sequence seq_ustore_union_0032 start with 10 maxvalue 200 increment by 2 cycle;

--step4:select目标列使用序列; expect:查询成功
select /*+parallel(4)*/ count(seq_ustore_union_0032.nextval) from t_ustore_union_0032 t1 join t_ustore_union_0032 on 1=1 group by t1.id
union all select id from t_ustore_union_0032;

--step5:清理环境;expect:成功
drop table t_ustore_union_0032 cascade;
drop sequence seq_ustore_union_0032;
drop procedure proc_ustore_union;